Home » SQL & PL/SQL » SQL & PL/SQL » not gettting output for showplanfrpspreadsheetcode11g (Oracle 12c, Solaris SPARC)
not gettting output for showplanfrpspreadsheetcode11g [message #663917] Mon, 26 June 2017 01:10 Go to next message
Gurubalan
Messages: 2
Registered: January 2017
Junior Member
Hi All,
I've bought the book "Oracle SQL performance Tuning and Optimization" by Kevin. I am not getting the output as expected below while running showplanfrpspreadsheecode11g.

Plan Filtered Actual
ID TABLE_NAME NUM_ROWS ROWCOUNT Cardinality Cardinality FRP


Here is what I did:
-------------------------------

sys@GDB> explain plan for select * from dept where deptno = 40;

Explained.

Elapsed: 00:00:00.01
sys@GDB> @showplanfrpspreadsheetcode11g

SQLTEXT
------------------------------------------------------------------------------------------------------------------------------------
with
frp_data as (
select ' 1' id,'SYS' table_owner,'DEPT' table_name,'DEPT' table_alias,100000 num_rows,count(*) rowcount,1000
cardinality,count(case when "DEPTNO"=40 then 1 end) filtered_cardinality from SYS.DEPT DEPT union all

select null,null,null,null,null,null,null,null from dual
)
select frp_data.*,round(frp_data.filtered_cardinality/case when frp_data.rowcount = 0 then cast(null as number) else frp_data.rowcou
nt end*100,1) actual_frp,decode(frp_data.filtered_cardinality,null,cast(null as number),round(frp_data.cardinality/case when frp_dat
a.num_rows = 0 then cast(null as number) else frp_data.num_rows end*100,1)) plan_frp

from frp_data
where id is not null
order by frp_data.id
/

10 rows selected.

Elapsed: 00:00:00.01
sys@GDB>

------
Am I missing something?

Thanks,
Guru.
Re: not gettting output for showplanfrpspreadsheetcode11g [message #663918 is a reply to message #663917] Mon, 26 June 2017 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Maybe you could send a PM to Kevin as we have not the script and so can't help.

Re: not gettting output for showplanfrpspreadsheetcode11g [message #663927 is a reply to message #663918] Mon, 26 June 2017 06:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Hi. My thanks to Barbara for letting me know there was a open question.

So that gen... script is a code generator. When it works correctly, it is supposed to produce code, which it certainly appears to have done for you. You run that code and you should get a FRP (Filtered Rows Percentage) spreadsheet. Here is the example from the book chapter #1.

                                                 Plan    Filtered Actual
ID    TABLE_NAME      NUM_ROWS   ROWCOUNT Cardinality Cardinality    FRP
----- ------------- ---------- ---------- ----------- ----------- ------
    8 EMP_DIM          6243035    6243035      240117      215414    3.5
    9 EMP_LOC_DIM       329699     329699      296337      329699  100.0
   15 EMPLR_LOC_DIM       8874       8874        8874        8872  100.0
   19 EMP_DIM          6243035    6243035      240117      236469    3.8
   21 EMP_LOC_DIM       329699     329699      251761      212993   64.6

5 rows selected.

Notice the different columns please. The ID line from the plan where a table will be feeding rows into query execution (in some form, consider a full index scan that never touches the table, is still giving you data from that table), the table name, then the metrics. These are taken from different places (DBA_TABLES, EMP table, PLAN_TABLE, EMP table filtering query). So you need to do two things:

1. examine if you are interested, in the details inside the generated code.  Try to see how the different pieces generate the data you will see.
2. run the FRP query and see what it gives you.

Looking at the first part of your generated FRP query:

select ' 1' id,'SYS' table_owner,'DEPT' table_name,'DEPT' table_alias,100000 num_rows,count(*) rowcount,1000
 cardinality,count(case when "DEPTNO"=40 then 1 end) filtered_cardinality from SYS.DEPT DEPT union all

We see that:

. table DEPT (which apparently is owned by SYS (naughty naughty))
. has 100,000 rows (so says dba_tables.num_rows)
. the FRP generated code plans to count the actual rows on the table by using COUNT(*), so you can compare that to num_rows to gauge accuracy of stats.
. your query plan line#1 thinks the filtering against DEPT (DEPTNO=40) will result in 1000 rows being returned
. the FRP generated code plans to count the number of rows returned after filtering by applying your filter criteria as seen noted in the plan table


Consider these queries.


select owner,table_name,num_rows NUM_ROWS from dba_tables where owner = 'SYS' and table_name = 'DEPT';

select 'SYS','DEPT',count(*) ROWCOUNT from sys.dept;

select id,object_owner,object_name,cardinality PLAN_CARDINALITY from plan_table order by line;

select 'SYS','DEPT',COUNT(*) FILTERED_CARDINALITY from sys.dept where deptno = 40;

These four queries are the basis of the FRP Spreadsheet. The generating code (the script) does queries #1/#3, and the generated code (what you got) will do queries #2/#4 and put the results together to give you the spreadsheet, calculating the % column at that time.

If you think there might be a bug somewhere (which is possible) try this first. Clearing out the plan table so that you only have one plan it when you generate code "might" help. I don't have to do it but early versions of the generator had an issue.

delete from plan_table;
explain plan for select * from dept where deptno = 40;
@showplanfrpspreadsheetcode11g -- or whatever is the current code you have.

All this is explained on chapter#1 as I recall. Good luck. Hope this gives you the answer you needed. Kevin
Re: not gettting output for showplanfrpspreadsheetcode11g [message #663956 is a reply to message #663927] Wed, 28 June 2017 00:05 Go to previous message
Gurubalan
Messages: 2
Registered: January 2017
Junior Member
Thank you Kevin for your quick response. I appreciate it.

I agree that you have given a detailed information on how we can populate the spreadsheet by running individual sql query (which I'm following now) without running showplanfrpspreadsheetcode11g. The code generator makes things easier as it provides populated data in the form of spreadsheet. As you've mentioned, I'll run the part of script and see how it works.

thanks for your time again.

Regards,
Guru.
Previous Topic: help needed with SQL code
Next Topic: ORA-01830 in XMLTABLE for DATE conversion
Goto Forum:
  


Current Time: Fri Mar 29 01:15:21 CDT 2024