Search

Top 60 Oracle Blogs

Recent comments

performance

Power BI 101- Logging and Tracing, Part II

So we went over locations and the basics of logging and tracing in Power BI.  I now want to know how to make more sense from the data.  In Oracle, we use a utility called TKProf, (along with others and a number of third party tools) to make sense of what comes from the logs.  SQL Server has Log Analytics and the profiler, but what can I do with Power BI?

First, let’s discuss what happens when we have actual activity.  In my first post, the system was pretty static.  This time I chose to open up a file with larger data refreshes from multiple sources, added tables, calculated columns and measures.  The one Access DB has over 10 million rows that is refreshed when I first open the PBIX file:

Power BI 101 – Log Files and Tracing

Knowing where log files are and how to turn on debugging is an essential part of any technical job and this goes for Power BI, too.  Remember, as I learn, so does everyone else….Come on, pretty please?

Power BI Desktop

Log files and traces can be accessed one of two ways-

  • Via the Power BI Application
  • Via File Explorer

In the Power BI application, go to File –> Options and Settings –> Options –> Diagnostics.

Crash and dump files are automatically stored with an option to disable them from this screen, but unsure why you’d ever want to do this.  If Power BI does crash, you would lose any valuable data on what the cause was.

Quiz Night

Here’s a question prompted by a recent thread on the ODevCom database forum – how many rows will Oracle sort (assuming you have enough rows to start with in all_objects) for the final query, and how many sort operations will that take ?


drop table t1 purge;

create table t1 nologging as select * from all_objects where rownum < 50000;

select owner, count(distinct object_type), count(distinct object_name) from t1 group by owner;

Try to resist the temptation of doing a cut-n-paste and running the code until after you’ve thought about the answer.

Complex materialized views and fast refresh

Just a quick discovery that came across the AskTOM “desk” recently. We have an outstanding bug in some instances of fast refresh materialized views when the definition of the materialized view references a standard view.

Here’s a simple demo of the issue – I’ll use a simplified version of the EMP and DEPT tables, linked by a foreign key in the usual way:

Lighty for PostgreSQL

If you follow this blog, you should know how I like Orachrome Lighty for Oracle, for its efficiency to monitor database performance statistics. Today Orachrome released the beta version of Lighty for Postgres: https://orachrome.com/news/la-beta-de-lighty-for-postgresql-est-ouverte/
The Cloud is perfect to do short tests with more resources than my laptop, especially the predictability of performance, then I started a Bitnami Postgres Compute service on the Oracle Cloud and did some tests with pgbench and pgio.

The installation is easy:

When WHEN went faster

Yeah…try saying that blog post title 10 times in a row as fast as you can Smile

But since we’re talking about doing things fast, this is just a quick post about a conversation I had a twitter yesterday about the WHEN clause in a trigger.

 

image

That is an easy benchmark to whip up – I just need a couple of tables, each with a simple a trigger differing only by their usage of the WHEN clause.  Here is my setup:

Power BI and the Speed(ier) Desktop

I can be an extremely impatient person about anything I think should be faster.

dbms_random

In a recent ODC thread someone had a piece of SQL that was calling dbms_random.string(‘U’,20) to generate random values for a table of 100,000,000 rows. The thread was about how to handle the ORA-30009 error (not enough memory for operation) that is almost inevitable when you use the “select from dual connect by level <= n” strategy for generating very large numbers of rows, but this example of calling dbms_random.string() so frequently prompted me to point out an important CPU saving , and then publicise through this blog a little known fact (or deduction) about the dbms_random.string() function.

Massive Delete

The question of how to delete 25 million rows from a table of one billion came up on the ODC database forum recently. With changes in the numbers of rows involved it’s a question that keeps coming back and I wrote a short series for AllthingsOracle a couple of years ago that discusses the issue. This is note is just a catalogue of links to the articles:

“Call me!” Many many times!

Some readers might recall that classic Blondie track “Call me”.  Of course, some readers might be wishing that I wouldn’t harp on about great songs from the 80’s. But bear with me, there is a (very tenuous) link to this post. If you haven’t heard the song, you can jump to the chorus right here.  Go on, I’ll wait until you get back. Smile